import sqlite3 as sq
from flask import Flask, request, jsonify
import numpy as np
import pandas as pd
from pandas.core.frame import DataFrame
import flask


app = Flask(__name__)
DATABASE_URI = ":memory:"
def returnData(success, data, cookie="", ToNone=True):
    if ToNone and len(data) <= 0:
        data = None
    jsonStr = {
        'success': success,
        'data': data
    }
    response = flask.make_response(flask.jsonify(jsonStr))
    if cookie:
        for key, value in cookie.items():
            response.set_cookie(key, value, max_age=3600 * 12)
    return response

def Query_train_ferature(sql):
    conn = sq.connect('TotaldataDB.db')
    cursor = conn.cursor()
    result = cursor.execute(sql)
    column = [tuple[0] for tuple in cursor.description]
    all_info = result.fetchall()
    return all_info, column

def Merge_train(sql):
    conn = sq.connect('Key_featureDB.db')
    cursor = conn.cursor()
    train_data=cursor.execute(sql)
    column = [tuple[0] for tuple in cursor.description]
    merge_train_data=train_data.fetchall()
    merge_train_data=np.array(merge_train_data)
    merge_train_data=merge_train_data.tolist()
    merge_train_data = DataFrame(merge_train_data)
    merge_train_data=merge_train_data.sample(frac=1)
    merge_train_data.columns = column
    fraud_df = merge_train_data.loc[merge_train_data['DEFAULT_LABEL'] == 1]
    non_fraud_df = merge_train_data.loc[merge_train_data['DEFAULT_LABEL'] == 0][:len(fraud_df)]
    return fraud_df,non_fraud_df,column

@app.route("/train_feature")
def train_feature():
    sql = "select * from train_feature "
    train_feature_data, col = Query_train_ferature(sql)
    train_feature_data=np.array(train_feature_data)
    yy = []
    for i in range(len(train_feature_data[0])):
        y = train_feature_data[:,i]
        y = y.tolist()
        yy.append(y)
    dict_list=dict(zip(col, yy))
    return returnData(True,str(dict_list))


@app.route("/train_lable")
def train_lable():
    sql = "select * from train_lable "
    train_feature_data, col = Query_train_ferature(sql)
    train_feature_data = np.array(train_feature_data)
    yy = []
    for i in range(len(train_feature_data[0])):
        y = train_feature_data[:,i]
        y = y.tolist()
        yy.append(y)
    dict_list=dict(zip(col, yy))
    return returnData(True,str(dict_list))


@app.route("/test_feature")
def test_feature():
    sql = "select * from test_feature "
    train_feature_data, col = Query_train_ferature(sql)
    n=len(train_feature_data[0])
    train_feature_data = np.array(train_feature_data)
    yy = []
    for i in range(n):
        y = train_feature_data[:, i]
        y=y.tolist()
        yy.append(y)
    dict_list=dict(zip(col, yy))
    return returnData(True,str(dict_list))


@app.route("/test_lable")
def test_lable():
    sql = "select * from test_lable "
    train_feature_data, col = Query_train_ferature(sql)
    train_feature_data = np.array(train_feature_data)
    yy = []
    for i in range(len(train_feature_data[0])):
        y = train_feature_data[:,i]
        y = y.tolist()
        yy.append(y)
    dict_list=dict(zip(col, yy))
    return returnData(True,str(dict_list))

@app.route("/key_feature")
def key_feature():
    sql="select * from train_data"
    merge_data_1,merge_data_0,col=Merge_train(sql)
    col=col[1:9:1]
    merge_data_1=merge_data_1[['v086','v183','v428','v883','v906','v682','v282','v647','v034']]
    merge_data_0=merge_data_0[['v086','v183','v428','v883','v906','v682','v282','v647','v034']]
    merge_data_1=np.array(merge_data_1)
    merge_data_0=np.array(merge_data_0)
    yy = []
    for i in range(len(merge_data_1[0])):
        y1 = merge_data_1[:, i]
        y0 = merge_data_0[:, i]
        y1 = y1.tolist()
        y0 = y0.tolist()
        y=[y0,y1]
        yy.append(y)
    dict_list=dict(zip(col, yy))
    return returnData(True,str(dict_list))

@app.route("/bigscreen/countDeviceNum", methods=["GET"])
def countUserNum():
    res = {
        "code": 200,
        "message": "OK"
    }
    info={"alarmNum": 790,
        "offlineNum":17,
        "onlineNum":681,
        "totalNum": 698}
    return returnData(True,str(info))

if __name__ == "__main__":
    app.run(debug=True)
